﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//请先添加引用

namespace Usas.DAL.Cms
{
    public class StatOperate
    {
        #region  查询指定的数据是否存在的方法
        public SqlConnection createCon()
        {
            SqlConnection con = new SqlConnection(Maticsoft.DBUtility.PubConstant.ConnectionString);
            return con;
        }

        /// <summary>
        /// 查询的数据是否已经存在
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>返回布尔值，true表示已经存在，false表示不存在</returns>
        public bool isData(string sql)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开数据库连接
            con.Open();
            //创建Command对象
            SqlCommand com = new SqlCommand(sql, con);
            //获取ExecuteScalar方法所返回的值
            int ex = Convert.ToInt32(com.ExecuteScalar());
            //关闭数据库连接
            con.Close();
            //判断整型变量并返回相应的布尔值
            if (ex > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region  返回表中指定列的方法
        /// <summary>
        /// 返回指定列的值
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <returns>列值</returns>
        public string getTier(string sql)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开数据库连接
            con.Open();
            //创建DataAdapter对象
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            //创建DataSet对象
            DataSet ds = new DataSet();
            //使用Fill方法填充DataSet对象
            sda.Fill(ds);
            //获取表中首行首列的数据
            string str = ds.Tables[0].Rows[0][0].ToString();
            //关闭数据库连接
            con.Close();
            return str;
        }
        #endregion

        #region  执行数据库的更新、插入、删除操作的方法
        /// <summary>
        /// 执行数据库中的更新、插入、删除操作
        /// </summary>
        /// <param name="sql">需要执行的SQL语句</param>
        /// <returns>返回布尔值，true表示已存在，false表示不存在</returns>
        public bool exSql(string sql)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开连接
            con.Open();
            //创建Command对象
            SqlCommand com = new SqlCommand(sql, con);
            //获取ExecuteNonQuery方法所返回的值
            int ex = com.ExecuteNonQuery();
            //关闭数据库连接
            con.Close();
            //判断所返回的值是否大于0，并返回相应的布尔值
            if (ex > 0)
            {
                return true;
            }
            else
                return false;
        }
        #endregion

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开连接
            con.Open();
            //创建Command对象
            SqlCommand com = new SqlCommand(SQLString, con);

            object obj = com.ExecuteScalar();
            //关闭数据库连接
            con.Close();
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }
        }

        #region  返回表中的所有数据的方法
        /// <summary>
        /// 返回表中的所有数据
        /// </summary>
        /// <param name="sql">需要执行的sql语句</param>
        /// <returns>返回DataSet对象</returns>
        public DataSet getRows(string sql)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开数据连接
            con.Open();
            //创建DataAdapter对象
            SqlDataAdapter sda = new SqlDataAdapter(sql, con);
            //创建DataSet对象
            DataSet ds = new DataSet();
            //通过Fill方法
            sda.Fill(ds);
            //关闭数据库连接
            con.Close();
            //返回DataSet对象
            return ds;
        }
        #endregion


        #region  更新或插入时段计数数据，用来记录当前时段的访问人数
        /// <summary>
        /// 用来增加当前时段的访问人数
        /// </summary>
        public void insertHour(string years, string months, string days)
        {
            //创建SQL语句，用来查询指定的月日是否存在
            string sqlSel = "select count(*) from tb_hourStat where years=" + years + " and months=" + months + " and days=" + days;
            //判断是否存在
            if (!isData(sqlSel))
            {
                //不存在，使用insert语句插入新的时段计数数据
                string sqlInsert = "insert into tb_hourStat(years,months,days," + getTime() + ") values(" + years + "," + months + "," + days + ",1) ";
                exSql(sqlInsert);
            }
            else
            {
                //存在，使用update语句更新时段计数数据
                //string sqlUp = "update tb_hourStat set " + getTime() + "=" + getTime() + "+1  where months = " + months + " and days=" + days + " and years=" + years;
                string sqlUp = "update tb_hourStat set " + getTime() + " = case when " + getTime() + " is null then 1 else " + getTime() + "+1 end where months = " + months + " and days=" + days + " and years=" + years;
                exSql(sqlUp);
            }
        }
        #endregion
        /// <summary>
        /// 返回时间字段
        /// </summary>
        /// <returns>返回表示当前时间的字段</returns>
        public string getTime()
        {
            string h = " ";
            //获取当前的时间进行判断
            switch (DateTime.Now.Hour)
            {
                case 0: h = "hour0"; break;
                case 1: h = "hour1"; break;
                case 2: h = "hour2"; break;
                case 3: h = "hour3"; break;
                case 4: h = "hour4"; break;
                case 5: h = "hour5"; break;
                case 6: h = "hour6"; break;
                case 7: h = "hour7"; break;
                case 8: h = "hour8"; break;
                case 9: h = "hour9"; break;
                case 10: h = "hour10"; break;
                case 11: h = "hour11"; break;
                case 12: h = "hour12"; break;
                case 13: h = "hour13"; break;
                case 14: h = "hour14"; break;
                case 15: h = "hour15"; break;
                case 16: h = "hour16"; break;
                case 17: h = "hour17"; break;
                case 18: h = "hour18"; break;
                case 19: h = "hour19"; break;
                case 20: h = "hour20"; break;
                case 21: h = "hour21"; break;
                case 22: h = "hour22"; break;
                case 23: h = "hour23"; break;
            }
            return h;
        }
        #region  执行存储过程，该存储过程用来创建月地域分析的视图
        /// <summary>
        /// 执行名为P_monthRegion的存储过程，该存储过程用来创建月地域分析的视图
        /// </summary>
        /// <param name="months">该参数表示当前的月份</param>
        public void execProcedure(string months)
        {
            //创建数据库连接
            SqlConnection con = createCon();
            //打开数据库
            con.Open();
            //创建Command对象
            SqlCommand com = new SqlCommand("P_monthRegion", con);
            //设置存储过程
            com.CommandType = CommandType.StoredProcedure;
            //设置参数类型及大小
            SqlParameter parm = new SqlParameter("@months", SqlDbType.VarChar, 10);
            //设置参数值
            parm.Value = months;
            //添加参数
            com.Parameters.Add(parm);
            //执行存储过程
            com.ExecuteNonQuery();
            //关闭数据库连接
            con.Close();
        }
        #endregion

        #region  流量统计
        public DataSet DayStat(string year, string months, string days, string strMonth)
        {
            string SqlDayStat = "select " + strMonth + " from tb_hourStat where 1=1 " + year + months + days;
            return getRows(SqlDayStat);
        }

        public object IpStat(string year, string months, string days, string i)
        {
            string SqlIpStat = "select count(id) from tb_dayStat WHERE 1=1 " + year + months + days + i;
            return GetSingle(SqlIpStat);
        }

        public DataSet ClientStat(string year, string months, string days)
        {
            string sql = "select count(id) as clientcount,systemName  from tb_system " +
                "left join tb_dayStat on tb_dayStat.systemId=tb_system.systemId where 1=1 " + year + months + days +
                " group by systemName";
            return getRows(sql);
        }

        public DataSet BrowserStat(string year, string months, string days)
        {
            string sql = "select count(id) as clientcount,browserName  from tb_browser " +
                "left join tb_dayStat on tb_dayStat.browserId=tb_browser.browserId where 1=1 " + year + months + days +
                " group by browserName";
            return getRows(sql);
        }

        public DataSet ArticleStat(string where, string sqlConn)
        {
            string sql = "select count(artid) as ct,empname,(select count(artid) from cmsarticle where ArtFormat !=1 and PublishStatus =1 and (IsVersion !=0 or IsVersion is null)) as ok from cmsarticle " +
                "inner join Usasdb.dbo.Employees as table2 " +
                "on cmsarticle.writer=table2.employeeid " +
                "where ArtFormat !=1 and PublishStatus =1 and (IsVersion !=0 or IsVersion is null) " + where +
                "group by writer,empname " +
                "order by ct desc";
            return getRows(sql);
        }

        public DataSet CmsTypeStat()
        {
            string sql = "select count(artid) as 'sc',CmsType.TypeId,CmsType.TypeName,(select count(artid) from cmsArticle where ArtFormat !=1 and PublishStatus =1 and (IsVersion !=0 or IsVersion is null)) as 'ac' from CmsType" +
                " inner join cmsArticle on CmsType.TypeId=cmsArticle.TypeId " +
                " where ArtFormat !=1 and PublishStatus =1 and (IsVersion !=0 or IsVersion is null) " +
                " group by CmsType.TypeId,CmsType.TypeName ";
            return getRows(sql);
        }
        #endregion

        #region 统计合计
        public object hourStat(string WHERE)
        {
            string sql = "select sum(isnull(hour0,0)) + sum(isnull(hour1,0)) + sum(isnull(hour2,0)) + sum(isnull(hour3,0)) + sum(isnull(hour4,0)) + sum(isnull(hour5,0)) + sum(isnull(hour6,0)) + sum(isnull(hour7,0)) + sum(isnull(hour8,0)) + sum(isnull(hour9,0)) " +
                "+ sum(isnull(hour10,0)) + sum(isnull(hour11,0)) + sum(isnull(hour12,0)) + sum(isnull(hour13,0)) + sum(isnull(hour14,0)) + sum(isnull(hour15,0)) + sum(isnull(hour16,0)) + sum(isnull(hour17,0)) + sum(isnull(hour18,0)) + sum(isnull(hour19,0)) + " +
                "sum(isnull(hour20,0)) + sum(isnull(hour21,0)) + sum(isnull(hour22,0)) + sum(isnull(hour23,0)) from tb_hourStat where " +
                WHERE;
            return GetSingle(sql);
        }
        #endregion
    }
}
